
-- Project: SQL Warehouse Database
-- Description:
	-- This is an SQL project that will have two tables called companyInfo and companysOrders.
	-- These tables will be connected by the companyId.
	-- Each of the ten comppanys will have 10 orders from each of the order types.
	-- These tables will be joined to show the customer and their order for each type.
-- Jamila Kandawala
-- Date: 2/19/2025

create database warehouse; -- This will create a database called warehouse, which has 2 tables inside of it.

use warehouse; -- This will allow me to use the warehouse database, which will work when I put in the tables.

-- This is creating the first table, which has 4 fields and the primary id. This table is called companyInfo, which contains the information about the company.
create table companyInfo(
	id int primary key auto_increment, -- This is the primary key and it is auto_increment, so it has no value, but will count by from 1 up.
	companyId varchar(7), -- This is creating a field called companyId, which will have a character limit of 7.
	companyName varchar(50),  -- This is creating a field called companyName, which will have a character limit of 50.
	companyAddress varchar(75),  -- This is creating a field called companyAddress, which will have a character limit of 75.
	companyEmail varchar(50)  -- This is creating a field called companyEmail, which will have a character limit of 50.
);

-- This is creating the second table called companysOrders, which will have 3 fields and the primary key that will be auto_increment, but it will be named orderId
create table companysOrders(
	orderId INT Primary Key auto_increment, -- This is creating a field called orderId that will be the primary key and it will be auto_increment, which will continue to increaase based on the nmber of values.
	companyId varchar(5),  -- This is creating a field called companyId, which will have a character limit of 7.
	orderCode varchar(10),  -- This is creating a field called orderCode, which will have a character limit of 10.
	dateOfOrder DATE -- This is a field called dateOfOrder, which is in the format of YY-MM-DD and will show the date of that specific order.
);

describe companyInfo; -- I am describing the table called companyInfo, so it will allow me to add values into this table after it is describes and the format of the table will be displayed like of the fields and the primary key.

describe companysOrders; -- I am describing the table called companysOrders, so that I will be able to add values and the fields/primary key will be displayed in a table format. 

-- This is used to insert values into the table called companyInfo. This table will also put the format of the dats.
insert into companyInfo 
(companyId, companyName, companyAddress, companyEmail) -- This is the format of how the data will be and it is showing the order for the values below.
values -- This is showing that below will have all the values for this table in the format above.

-- This is all the data for each of the companys. Also, it has the name, address, and email of each company too.
('Co1', 'Tech Tonic', '6 Skyscraper Hill Rd Brooks Maine(ME) 04921', 'tech_tonic@gmail.com'),
('Co2', 'Pixel Pioneers', '3703 73rd Ave NE Marysville Washington(WA) 98270', 'pixel_pioneers@gmail.com'),
('Co3', 'Cloud Canvas', '28758 Veto Rd Elkmont Alabama(AL) 35620', 'cloud_canvas@gmail.com'),
('Co4', 'Data Dynamo', '1205 Johnson St Hartford Kentucky(KY) 42347', 'data_dynamo@gmail.com'),
('Co5', 'AI Architects', '63 Maple St Douglas Massachusetts(MA) 01516', 'AI_architects@gmail.com'),
('Co6', 'Cloud Nexus', '3701 Aurora St Haltom City Texas(TX) 76117', 'cloud_nexus@gmail.com'),
('Co7', 'CyberSphere', '26 Marion Ln Streamwood Illinois(IL) 60107', 'cyber_sphere@gmail.com'),
('Co8', 'App Innovators', '6101 Edgebrook Dr Orlando Florida(FL) 32809', 'app_innovators@gmail.com'),
('Co9', 'Neural Networks', '106 E Robin Ct South Mills North Carolina(NC) 27976', 'neutral_networks@gamil.com'),
('Co10', 'Circuit Creators', '1707 Central St Bethany Missouri(MO) 64424', 'circuit_creators@gmail.com')
;

-- This is inserting values into the table called companyOrders. Also, this table will have a format for the data below.
insert into companysOrders 
(orderId, companyId, orderCode, dateOfOrder) -- This is the format of the values, which will show how it will be listed. It will be listed as the orderId, the companyId, the orderCode, and the dateOfOrder.
values  -- These are all the values/data that will be inputted into the table based on the format above.

-- These are all the 10 companies with the order type of OC1.
(1, 'Co1', 'OC1', '2025-04-18'),
(2, 'Co2', 'OC1', '2025-08-02'),
(3, 'Co3', 'OC1', '2025-12-31'),
(4, 'Co4', 'OC1', '2025-11-22'),
(5, 'Co5', 'OC1', '2025-03-21'),
(6, 'Co6', 'OC1', '2025-09-30'),
(7, 'Co7', 'OC1', '2025-02-28'),
(8, 'Co8', 'OC1', '2025-10-12'),
(9, 'Co9', 'OC1', '2025-05-03'),
(10, 'Co10', 'OC1', '2025-07-04'),

-- These are all the 10 companies with the order type of OC2.
(11, 'Co1', 'OC2', '2025-12-03'),
(12, 'Co2', 'OC2', '2025-03-22'),
(13, 'Co3', 'OC2', '2025-08-12'),
(14, 'Co4', 'OC2', '2025-05-19'),
(15, 'Co5', 'OC2', '2025-10-06'),
(16, 'Co6', 'OC2', '2025-04-01'),
(17, 'Co7', 'OC2', '2025-07-09'),
(18, 'Co8', 'OC2', '2025-09-11'),
(19, 'Co9', 'OC2', '2025-06-17'),
(20, 'Co10', 'OC2', '2025-11-26'),

-- These are all the 10 companies with the order type of OC3.
(21, 'Co1', 'OC3', '2025-08-21'),
(22, 'Co2', 'OC3', '2025-05-31'),
(23, 'Co3', 'OC3', '2025-04-25'),
(24, 'Co4', 'OC3', '2025-07-15'),
(25, 'Co5', 'OC3', '2025-09-29'),
(26, 'Co6', 'OC3', '2025-02-27'),
(27, 'Co7', 'OC3', '2025-12-02'),
(28, 'Co8', 'OC3', '2025-06-20'),
(29, 'Co9', 'OC3', '2025-10-10'),
(30, 'Co10', 'OC3', '2025-03-07'),

-- These are all the 10 companies with the order type of OC4.
(31, 'Co1', 'OC4', '2025-05-29'),
(32, 'Co2', 'OC4', '2025-10-13'),
(33, 'Co3', 'OC4', '2025-03-09'),
(34, 'Co4', 'OC4', '2025-03-24'),
(35, 'Co5', 'OC4', '2025-08-19'),
(36, 'Co6', 'OC4', '2025-06-09'),
(37, 'Co7', 'OC4', '2025-12-18'),
(38, 'Co8', 'OC4', '2025-04-12'),
(39, 'Co9', 'OC4', '2025-11-25'),
(40, 'Co10', 'OC4', '2025-09-01'),

-- These are all the 10 companies with the order type of OC5.
(41, 'Co1', 'OC5', '2025-07-27'),
(42, 'Co2', 'OC5', '2025-09-15'),
(43, 'Co3', 'OC5', '2025-12-26'),
(44, 'Co4', 'OC5', '2025-11-23'),
(45, 'Co5', 'OC5', '2025-10-21'),
(46, 'Co6', 'OC5', '2025-06-17'),
(47, 'Co7', 'OC5', '2025-05-16'),
(48, 'Co8', 'OC5', '2025-03-06'),
(49, 'Co9', 'OC5', '2025-08-03'),
(50, 'Co10', 'OC5', '2025-04-18'),

-- These are all the 10 companies with the order type of OC6.
(51, 'Co1', 'OC6', '2025-08-12'),
(52, 'Co2', 'OC6', '2025-05-28'),
(53, 'Co3', 'OC6', '2025-10-15'),
(54, 'Co4', 'OC6', '2025-03-10'),
(55, 'Co5', 'OC6', '2025-04-21'),
(56, 'Co6', 'OC6', '2025-11-16'),
(57, 'Co7', 'OC6', '2025-02-25'),
(58, 'Co8', 'OC6', '2025-12-29'),
(59, 'Co9', 'OC6', '2025-06-13'),
(60, 'Co10', 'OC6', '2025-12-09')
;


-- This is selecting the orderId, companyName, and the dateOfOrder from the companysOrder table and remnaming all of them, so that it is displayed named properly. Also, the data from these three fields are used in the table, so that it can be organized below.
Select companysOrders.orderId as 'Order ID', companyInfo.companyName as 'Customer Name', companysOrders.orderCode as 'Order Code', companysOrders.dateOfOrder as 'Date of Order'
From companysOrders join companyInfo -- This is joining the two tables called companysOrders and the companyInfo, which will allow me to use the data from both of these tables.
Where companysOrders.companyId = companyInfo.companyId -- This is making the companyId from both the companysOrders and companyInfo tables, equal to each other, so that the tables are able to join.
Order By companyInfo.companyName, companysOrders.orderCode; -- This is ordering all the data based o the company name and the order code, so that the names of the companies are ordered first then the order code of that specific company.

-- This is selecting the orderId, companyName, and the dateOfOrder from the companysOrder table and remnaming all of them, so that it is displayed named properly. Also, the data from these three fields are used in the table, so that it can be organized below.
Select companysOrders.orderId as 'Order ID', companyInfo.companyName as 'Customer Name', companysOrders.orderCode as 'Order Code', companysOrders.dateOfOrder as 'Date of Order'
From companysOrders join companyInfo -- This is joining the two tables called companysOrders and the companyInfo, which will allow me to use the data from both of these tables.
 -- This is making the companyId from both the companysOrders and companyInfo tables, equal to each other, so that the tables are able to join. This is also finding all the companyId's that are co7, so that it will be organized. This is where you can choose any of the companies to see the orders of that specific company.
Where companysOrders.companyId = companyInfo.companyId And companyInfo.companyId = 'Co7' 
Order By companysOrders.orderCode; -- This is ordering all the data by the order code, so that it will go by OC1 to OC6.

-- This is selecting the orderId, companyName, and the dateOfOrder from the companysOrder table and remnaming all of them, so that it is displayed named properly. Also, the data from these three fields are used in the table, so that it can be organized below.
Select companysOrders.orderId as 'Order ID', companyInfo.companyName as 'Customer Name', companysOrders.orderCode as 'Order Code', companysOrders.dateOfOrder as 'Date of Order'
 -- This is joining the two tables called companysOrders and the companyInfo, which will allow me to use the data from both of these tables.
From companysOrders join companyInfo
-- This is making the companyId from both the companysOrders and companyInfo tables, equal to each other, so that the tables are able to join. This is also organizing the data by the order code to be Oc4 for any of the companies to be displayed.
Where companysOrders.companyId = companyInfo.companyId And companysOrders.orderCode = 'OC4' 
Order By companyInfo.companyName; -- This is ordering all the data of the companies with Oc4 by the company name in alphabetical order.

-- This is selecting the orderCode for the companysOrders and makeing them named to Order Code, so that it will coount each of the orders for that specific type of order and then display it.
Select companysOrders.orderCode as 'Order Code', COUNT(*) as 'Order Quantity' 
From companysOrders join companyInfo -- This is joining the two tables called companysOrders and the companyInfo, which will allow me to use the data from both of these tables.
Where companysOrders.companyId = companyInfo.companyId -- This is making the companyId from both the companysOrders and companyInfo tables, equal to each other, so that the tables are able to join.
Group By companysOrders.orderCode; -- This is grouping all the data by the order code, so that it will go by OC1 to OC6, so that the data will be from that order group.


Drop database warehouse; -- This is dropping, so deleting the database called warehouse, which will delete the two tables inside the database.

